import equation.Equation;
import equation.EquationFactory;
import exercise.Exercise;
import exercise.Iterator;

import java.sql.*;

public class DatabaseOperator {

    // 预定义的SQL语句，含有占位符 ‘？’ 在 '查', '增'， '删', '改', 查找数据是否已存在的时候使用
    private static final String sqlStatement_selectAll = "SELECT * FROM equation";
    private static final String sqlStatement_insert = "INSERT equation VALUES (?, ?, ?)";
    private static final String sqlStatement_delete = "DELETE FROM equation WHERE operandA = ? " +
            "AND operandB = ? AND operator = ?";
    private static final String sqlStatement_update = "UPDATE equation SET operandA = ?, operandB = ?, operator = ? " +
            "WHERE operandA = ? AND operandB = ? AND operator = ?";
    private static final String sqlStatement_selectSpecific = "SELECT * FROM equation WHERE operandA = ? " +
            "AND operandB = ? AND operator = ?";


    // 数据库的url，用户名，密码
    private static final String mysql_url = "jdbc:mysql://localhost:3306/mydb";
    private static final String user = "root";
    private static final String password = "root";

    // 声明的几个在每次执行SQL方法时会用到的属性
    private static Connection connection = null;
    private static ResultSet resultSet = null;
    private static PreparedStatement preparedStatement = null;
    private static Statement statement = null;

    // 单例模式
    private static final DatabaseOperator instance = new DatabaseOperator();
    public static DatabaseOperator getInstance() {
        return instance;
    }
    // 构造函数内实现加载驱动和链接数据库操作
    private DatabaseOperator() {
        // 驱动加载
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            // 链接数据库
            connection = DriverManager.getConnection(mysql_url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void clean() {
        // 执行完毕后close各个对象
        try {
            if (connection != null) {
                connection.close();
            }

            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (statement != null){
                statement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 对数据库中指定算式更新
    public void update(Equation equation, Equation newEquation) {
        try {
            preparedStatement = connection.prepareStatement(sqlStatement_update);
            preparedStatement.setInt(1, newEquation.getOperandA());
            preparedStatement.setInt(2, newEquation.getOperandB());
            preparedStatement.setString(3, String.valueOf(newEquation.getOperator()));
            preparedStatement.setInt(4, equation.getOperandA());
            preparedStatement.setInt(5, equation.getOperandB());
            preparedStatement.setString(6, String.valueOf(equation.getOperator()));
            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 向数据库中插入算式
    public void insert(Equation equation) throws SQLException {
        // 如果数据库中已存在，则不插入，直接返回
        if (existSpecific(equation)) {
            return;
        }
        try {
            preparedStatement = connection.prepareStatement(sqlStatement_insert);
            preparedStatement.setInt(1, equation.getOperandA());
            preparedStatement.setInt(2, equation.getOperandB());
            preparedStatement.setString(3, String.valueOf(equation.getOperator()));
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 查询指定算式, 判断表中是否存在
    public boolean existSpecific(Equation equation) throws SQLException {
        PreparedStatement p;
        try {
            p = connection.prepareStatement(sqlStatement_selectSpecific);
            p.setInt(1, equation.getOperandA());
            p.setInt(2, equation.getOperandB());
            p.setString(3, String.valueOf(equation.getOperator()));
            resultSet = p.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet.first();
    }

    // 删除数据库中某一算式
    public void delete(Equation equation) {
        try {
            preparedStatement = connection.prepareStatement(sqlStatement_delete);
            preparedStatement.setInt(1, equation.getOperandA());
            preparedStatement.setInt(2, equation.getOperandB());
            preparedStatement.setString(3, String.valueOf(equation.getOperator()));
            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    // 查询所有算式
    public void selectAll() {
        System.out.println("All Equation Table Elements :");
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sqlStatement_selectAll);
            while (resultSet.next()) {
                EquationFactory equationFactory = new EquationFactory();
                String[] keyWords = {"AddEquation", "SubEquation", "MultiplyEquation", "DivideEquation"};
                Character[] keyOperator = {'+', '-', '*', '/'};
                Equation equation = null;
                Integer operandA = resultSet.getInt("operandA");
                Integer operandB = resultSet.getInt("operandB");
                char operator = resultSet.getString("operator").charAt(0);
                String keyWord = null;
                for (int i = 0; i < 4; i++) {
                    if (operator == keyOperator[i]) {
                        keyWord = keyWords[i];
                    }
                }
                System.out.println(equationFactory.getEquation(keyWord, operandA, operandB));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 加载给定数量的算式
    public Exercise loadExercises(int number) {
        Exercise exercise = new Exercise();
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sqlStatement_selectAll);
            while (resultSet.next() && number != 0) {
                EquationFactory equationFactory = new EquationFactory();
                String[] keyWords = {"AddEquation", "SubEquation", "MultiplyEquation", "DivideEquation"};
                Character[] keyOperator = {'+', '-', '*', '/'};
                Integer operandA = resultSet.getInt("operandA");
                Integer operandB = resultSet.getInt("operandB");
                char operator = resultSet.getString("operator").charAt(0);
                String keyWord = null;
                for (int i = 0; i < 4; i++) {
                    if (operator == keyOperator[i]) {
                        keyWord = keyWords[i];
                    }
                }
                exercise.add(equationFactory.getEquation(keyWord, operandA, operandB));
                number--;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("Loaded successfully");
        return exercise;
    }

    // 保存生成的算式
    public void saveExercise(Exercise exercise) throws SQLException {
        Iterator iterator = exercise.getIterator();
        while (iterator.existNext()) {
            insert((Equation) iterator.next());
        }
        System.out.println("Saved successfully");
    }
}
